﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    public class clsKetNoi
    {
        SqlConnection con = new SqlConnection();
        public clsKetNoi()
        {
            string sql = "Server=(local)\\SQLEXPRESS;Database=QuanLy;Trusted_Connection=True;";
            con.ConnectionString = sql;
            if(con.State == ConnectionState.Closed)
            {
                con.Open();
            }
        }
        public void close()//Hàm đóng kết nối
        {
            con.Dispose();
        }

        
        public DataTable LayDuLieu(string sql)
        {
            SqlCommand cm = new SqlCommand(sql,con);
            cm.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter(cm);
            DataTable dt = new DataTable();
            da.Fill(dt);
            cm.Dispose();
            da.Dispose();
            dt.Dispose();
            return dt;
        }
        public DataTable LayDuLieu(string sql,string[] name, object[] value, int Nparameter)//Lấy data có tham số truyền vào
        {
            SqlCommand cm = new SqlCommand(sql,con);
            cm.CommandType = CommandType.StoredProcedure;
            for (int i = 0; i < Nparameter; i++ )
            {
                cm.Parameters.AddWithValue(name[i],value[i]);
            }
            SqlDataAdapter da = new SqlDataAdapter(cm);
            DataTable dt = new DataTable();
            da.Fill(dt);
            cm.Dispose();
            da.Dispose();
            dt.Dispose();
            return dt;
        }
  


        public int Update(string sql)
        {
            SqlCommand cm = new SqlCommand(sql,con);
            cm.CommandType = CommandType.StoredProcedure;
            cm.Dispose();
            return cm.ExecuteNonQuery();
        }
        public int Update(string sql,string[] name,object[] value,int Nparamerter)
        {
            SqlCommand cm = new SqlCommand(sql,con);
            cm.CommandType = CommandType.StoredProcedure;
            for (int i = 0; i < Nparamerter;i++ )
            {
                cm.Parameters.AddWithValue(name[i],value[i]);
            }
            cm.Dispose();
            return cm.ExecuteNonQuery();

        }
       
    }
}
